import 'dart:io';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';
import 'package:path/path.dart';
import 'package:flutter/foundation.dart';
import 'package:path_provider/path_provider.dart';
import '../models/member.dart';
import '../models/task.dart';

class DatabaseService {
  static final DatabaseService _instance = DatabaseService._internal();
  static Database? _database;

  factory DatabaseService() {
    return _instance;
  }

  DatabaseService._internal();

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  Future<Database> _initDatabase() async {
    try {
      // 使用应用程序文档目录
      final documentsDirectory = await getApplicationDocumentsDirectory();
      final path = join(documentsDirectory.path, 'todo_app.db');
      
      // 确保目录存在
      await Directory(dirname(path)).create(recursive: true);
      
      debugPrint('Database path: $path'); // 添加日志
      
      return await databaseFactoryFfi.openDatabase(
        path,
        options: OpenDatabaseOptions(
          version: 3,
          onCreate: _createDb,
          onUpgrade: _upgradeDb,
          onOpen: (db) {
            debugPrint('Database opened successfully');
          },
        ),
      );
    } catch (e) {
      debugPrint('Database initialization error: $e');
      rethrow;
    }
  }

  Future<void> _createDb(Database db, int version) async {
    // 创建成员表
    await db.execute('''
      CREATE TABLE members(
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT NOT NULL,
        password TEXT,
        role TEXT NOT NULL,
        created_at TEXT NOT NULL,
        is_active INTEGER NOT NULL
      )
    ''');

    // 创建任务表
    await db.execute('''
      CREATE TABLE tasks(
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        description TEXT NOT NULL,
        created_at TEXT NOT NULL,
        due_date TEXT,
        is_completed INTEGER NOT NULL DEFAULT 0,
        color INTEGER NOT NULL,
        priority TEXT NOT NULL,
        assigned_to TEXT,
        position INTEGER NOT NULL DEFAULT 0,
        FOREIGN KEY (assigned_to) REFERENCES members (id)
      )
    ''');

    // 创建默认管理员账号
    await db.insert('members', {
      'id': 'admin',
      'name': 'admin',
      'email': 'admin@example.com',
      'password': '123456',
      'role': MemberRole.admin.toString(),
      'created_at': DateTime.now().toIso8601String(),
      'is_active': 1,
    });

    // 创建默认成员账号
    await db.insert('members', {
      'id': 'member',
      'name': 'member',
      'email': 'member@example.com',
      'password': '123456',
      'role': MemberRole.member.toString(),
      'created_at': DateTime.now().toIso8601String(),
      'is_active': 1,
    });
  }

  Future<void> _upgradeDb(Database db, int oldVersion, int newVersion) async {
    // 处理数据库升级
    if (oldVersion < 2) {
      // 添加 password 列
      await db.execute('ALTER TABLE members ADD COLUMN password TEXT');
    }
    if (oldVersion < 3) {
      // 备份旧的任务数据
      final List<Map<String, dynamic>> oldTasks = await db.query('tasks');
      
      // 删除旧的任务表
      await db.execute('DROP TABLE IF EXISTS tasks');
      
      // 创建新的任务表
      await db.execute('''
        CREATE TABLE tasks(
          id TEXT PRIMARY KEY,
          title TEXT NOT NULL,
          description TEXT NOT NULL,
          created_at TEXT NOT NULL,
          due_date TEXT,
          is_completed INTEGER NOT NULL DEFAULT 0,
          color INTEGER NOT NULL,
          priority TEXT NOT NULL,
          assigned_to TEXT,
          position INTEGER NOT NULL DEFAULT 0,
          FOREIGN KEY (assigned_to) REFERENCES members (id)
        )
      ''');

      // 恢复旧的任务数据，并加 position 字段
      if (oldTasks.isNotEmpty) {
        // 分别处理已成和未完成的任务
        final completedTasks = oldTasks.where((t) => t['is_completed'] == 1).toList();
        final pendingTasks = oldTasks.where((t) => t['is_completed'] == 0).toList();

        // 恢复未完成任务
        for (var i = 0; i < pendingTasks.length; i++) {
          final task = pendingTasks[i];
          await db.insert('tasks', {
            ...task,
            'position': i,
          });
        }

        // 恢复已完成任务
        for (var i = 0; i < completedTasks.length; i++) {
          final task = completedTasks[i];
          await db.insert('tasks', {
            ...task,
            'position': i,
          });
        }
      }
    }
  }

  // CRUD operations for members
  Future<String> insertMember(Member member) async {
    final db = await database;
    await db.insert(
      'members',
      member.toMap(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
    return member.id;
  }

  Future<List<Member>> getAllMembers() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query('members');
    return List.generate(maps.length, (i) => Member.fromMap(maps[i]));
  }

  Future<Member?> getMember(String id) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'members',
      where: 'id = ?',
      whereArgs: [id],
    );

    if (maps.isEmpty) return null;
    return Member.fromMap(maps.first);
  }

  Future<int> updateMember(Member member) async {
    final db = await database;
    return await db.update(
      'members',
      member.toMap(),
      where: 'id = ?',
      whereArgs: [member.id],
    );
  }

  Future<int> deleteMember(String id) async {
    final db = await database;
    return await db.delete(
      'members',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  // 用于调试的辅助方法
  Future<void> printAllMembers() async {
    final members = await getAllMembers();
    print('Current members in database:');
    for (var member in members) {
      print('ID: ${member.id}, Name: ${member.name}, Email: ${member.email}, Role: ${member.role}');
    }
  }

  // 添加任务相关的 CRUD 操作
  Future<String> insertTask(Task task) async {
    final db = await database;
    await db.transaction((txn) async {
      // 获取当前最大位置
      final result = await txn.rawQuery('''
        SELECT COALESCE(MAX(position), -1) as maxPos 
        FROM tasks 
        WHERE is_completed = ?
      ''', [task.isCompleted ? 1 : 0]);
      
      final maxPosition = (result.first['maxPos'] as int?) ?? -1;
      final newPosition = maxPosition + 1;
      
      // 插入任务，设置正确的位置
      await txn.insert(
        'tasks',
        {...task.toMap(), 'position': newPosition},
        conflictAlgorithm: ConflictAlgorithm.replace,
      );
    });
    return task.id;
  }

  Future<List<Task>> getAllTasks() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'tasks',
      orderBy: 'is_completed ASC, position ASC',  // 先按完成状态，再按位置排序
    );
    return List.generate(maps.length, (i) => Task.fromMap(maps[i]));
  }

  Future<List<Task>> getCompletedTasks() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'tasks',
      where: 'is_completed = ?',
      whereArgs: [1],
    );
    return List.generate(maps.length, (i) => Task.fromMap(maps[i]));
  }

  Future<int> updateTask(Task task) async {
    final db = await database;
    return await db.update(
      'tasks',
      task.toMap(),
      where: 'id = ?',
      whereArgs: [task.id],
    );
  }

  Future<int> deleteTask(String id) async {
    final db = await database;
    return await db.delete(
      'tasks',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  // 添加更新任务位置的方法
  Future<void> reorderTasks(String taskId, int newPosition) async {
    final db = await database;
    await db.transaction((txn) async {
      // 获取当前任务的信息
      final List<Map<String, dynamic>> taskResult = await txn.query(
        'tasks',
        where: 'id = ?',
        whereArgs: [taskId],
        limit: 1,
      );
      
      if (taskResult.isEmpty) return;
      
      final task = taskResult.first;
      final oldPosition = task['position'] as int;
      final isCompleted = task['is_completed'] as int;
      
      // 如果位置相同，不需要移动
      if (oldPosition == newPosition) return;

      // 获取同状态的所有任务
      final List<Map<String, dynamic>> allTasks = await txn.query(
        'tasks',
        where: 'is_completed = ?',
        whereArgs: [isCompleted],
        orderBy: 'position ASC',
      );

      // 计算实际的移动范围
      final minPosition = allTasks.first['position'] as int;
      final maxPosition = allTasks.last['position'] as int;
      final targetPosition = newPosition.clamp(minPosition, maxPosition);
      
      // 更新区间内所有任务的位置
      if (oldPosition < targetPosition) {
        // 向后移动：将区间内的任务位置减1
        await txn.execute('''
          UPDATE tasks 
          SET position = position - 1 
          WHERE position > ? AND position <= ? 
          AND is_completed = ? 
          AND id != ?
        ''', [oldPosition, targetPosition, isCompleted, taskId]);
      } else {
        // 向前移动：将区间内的任务位置加1
        await txn.execute('''
          UPDATE tasks 
          SET position = position + 1 
          WHERE position >= ? AND position < ? 
          AND is_completed = ? 
          AND id != ?
        ''', [targetPosition, oldPosition, isCompleted, taskId]);
      }
      
      // 更新当前任务到新位置
      await txn.update(
        'tasks',
        {'position': targetPosition},
        where: 'id = ?',
        whereArgs: [taskId],
      );
    });
  }
} 